package com.fsu.database.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Singleton Data Access Object (DAO) for Media. This class should never be
 * used explicitly, but interfaced through the DatabaseManager 
 * class, instead.
 * @author Bonazza
 *
 */
public class MediaDAO {
	
	/**
	 * Singleton instance object
	 */
	private static MediaDAO instance;
	/**
	 * SQL Database connection object.
	 */
	private Connection con;
	
	/**
	 * Constructor
	 * @param c
	 */
	private MediaDAO(Connection c) {
		con = c;
	}
	
	/**
	 * Retrieve the singleton MediaDAO instance
	 * @param c
	 * @return The singleton instance of MediaDAO
	 */
	public static synchronized MediaDAO getInstance(Connection c) {
		if(instance == null)
			instance = new MediaDAO(c);
		
		return instance;
	}
	
	/**
	 * Retrieve a list of all media in the database (books and dvds)
	 * @return List of all media in the database
	 * @throws SQLException
	 */
	public List<Media> getMedia() throws SQLException {
		List<Media> mediaList = new ArrayList<Media>();
		
		List<Book> books = getBooks();
		List<DVD> dvds = getDvds();
		
		mediaList.addAll(books);
		mediaList.addAll(dvds);
		
		return mediaList;
	}
	
	/**
	 * Retrieve a list of all books in the database
	 * @return List of all books in the database
	 * @throws SQLException
	 */
	public List<Book> getBooks() throws SQLException {
		final List<Book> books = new ArrayList<Book>();
		final String q = "SELECT m.Title, m.Year, m.Price, b.isbn, b.subject, b.url FROM Books_PublishedBy b, Media m WHERE b.Title = m.Title AND b.Year = m.Year";
		final String pubq = "SELECT p.name, p.address, p.phone FROM Publishers p WHERE p.url = ?";
		final String authq = "SELECT a.name, a.address  FROM Authors a, Written_By w WHERE a.name = w.pname AND a.address = w.paddress AND w.Book_Title = ? AND w.Published_Year = ?";
		
		Statement stmt = con.createStatement();
		ResultSet cursor = stmt.executeQuery(q);
		
		while(cursor.next()) {
			String title = cursor.getString(1);
			int year = cursor.getInt(2);
			
			float price = cursor.getFloat(3);
			String isbn = cursor.getString(4);
			String subject = cursor.getString(5);
			String url = cursor.getString(6);
			PreparedStatement pstmt = con.prepareStatement(pubq);
			pstmt.setString(1, url);
			ResultSet c2 = pstmt.executeQuery();
			Publisher pub = null;
			if (c2.next()) {
				String pname = c2.getString(1);
				String paddress = c2.getString(2);
				String pphone = c2.getString(3);
				
				pub = new Publisher(url, pname, paddress, pphone);
			}
			
			PreparedStatement authStmt = con.prepareStatement(authq);
			authStmt.setString(1, title);
			authStmt.setInt(2, year);
			ResultSet c3 = authStmt.executeQuery();
			
			List<Author> authors = new ArrayList<Author>();
			while (c3.next()) {
				String aname = c3.getString(1);
				String aaddress = c3.getString(2);
				authors.add(new Author(aname, aaddress));
			}
			Book book = new Book(title, year, price, isbn, subject, pub, authors);
			books.add(book);
		}
		
		return books;
	}
	
	/**
	 * Retrieve a list of all DVDs in the database
	 * @return List of all DVDs in the database
	 * @throws SQLException
	 */
	public List<DVD> getDvds() throws SQLException {
		final List<DVD> dvds = new ArrayList<DVD>();
		final String q = "SELECT m.Title, m.Year, m.Price, d.director, d.genre FROM DVDs d, Media m WHERE d.Title = m.Title AND d.Year = m.Year";
		final String castq = "SELECT c.name, c.address FROM Cast c, Casted_By cb WHERE c.name = cb.name AND c.address = cb.address AND cb.Title = ? AND cb.Year = ?";
		
		Statement stmt = con.createStatement();
		ResultSet cursor = stmt.executeQuery(q);
		
		while(cursor.next()) {
			String title = cursor.getString(1);
			int year = cursor.getInt(2);
			float price = cursor.getFloat(3);
			String director = cursor.getString(4);
			String genre = cursor.getString(5);
			PreparedStatement pstmt = con.prepareStatement(castq);
			pstmt.setString(1, title);
			pstmt.setInt(2, year);
			ResultSet c2 = pstmt.executeQuery();
			List<CastMember> cast = new ArrayList<CastMember>();
			
			while (c2.next()) {
				String cname = c2.getString(1);
				String caddress = c2.getString(2);
				cast.add(new CastMember(cname, caddress));
			}
			
			DVD dvd = new DVD(title, year, price, director, genre, cast);
			dvds.add(dvd);
		}
		
		return dvds;
	}
	
	/**
	 * Retrieve a list of all registered authors
	 * @return a list of registered authors
	 * @throws SQLException
	 */
	public List<Author> getAuthors() throws SQLException {
		final List<Author> authors = new ArrayList<Author>();
		final String q = "SELECT a.Name, a.Address FROM Authors a";
		
		Statement stmt = con.createStatement();
		ResultSet cursor = stmt.executeQuery(q);
		
		while (cursor.next()) {
			String name = cursor.getString(1);
			String addy = cursor.getString(2);
			
			authors.add(new Author(name, addy));
		}
		
		return authors;
	}
	
	/**
	 * Retrieve a list of all registered publishers
	 * @return a list of all publishers
	 * @throws SQLException
	 */
	public List<Publisher> getPublishers() throws SQLException {
		final List<Publisher> pubs = new ArrayList<Publisher>();
		final String q = "SELECT p.URL, p.Name,  p.Address, p.Phone FROM Publishers p";
		
		Statement stmt = con.createStatement();
		ResultSet cursor = stmt.executeQuery(q);
		
		while(cursor.next()) {
			String url = cursor.getString(1);
			String name = cursor.getString(2);
			String addy = cursor.getString(3);
			String phone = cursor.getString(4);
			
			pubs.add(new Publisher(url, name, addy, phone));
		}
		
		return pubs;
	}
	
	/**
	 * Retrieve a list of all cast 
	 * @return a list of cast members
	 * @throws SQLException
	 */
	public List<CastMember> getCast() throws SQLException {
		final List<CastMember> cast = new ArrayList<CastMember>();
		final String q = "SELECT c.Name, c.Address FROM Cast c";
		
		Statement stmt = con.createStatement();
		ResultSet cursor = stmt.executeQuery(q);
		
		while (cursor.next()) {
			String name = cursor.getString(1);
			String addy = cursor.getString(2);
			
			cast.add(new CastMember(name, addy));
		}
		
		return cast;
	}
	
	/**
	 * get a list of the top 10 media sold
	 * @return a list of top 10 media sold
	 * @throws SQLException
	 */
	public List<Media> getTop10Sold() throws SQLException {
		List<Media> result = new ArrayList<Media>();
		List<Book> books = getBooks();
		List<DVD> dvds = getDvds();
		final String q = "SELECT o.Media_Title, o.Media_Year, COUNT(*) AS count FROM Ordered o GROUP BY o.Media_Title, o.Media_Year ORDER BY count DESC";
		
		Statement stmt = con.createStatement();
		ResultSet cursor = stmt.executeQuery(q);
		int i = 0;
		while (cursor.next() && i < 10) {
			String title = cursor.getString(1);
			int year = cursor.getInt(2);
			int count = cursor.getInt(3);
			System.out.println(count);
			
			for (Book b : books) {
				if (b.getTitle().equals(title) && b.getYear() == year) {
					result.add(b);
				}
			}
			
			for (DVD d : dvds) {
				if(d.getTitle().equals(title) && d.getYear() == year) {
					result.add(d);
				}
			}
		}
		
		return result;
	}
	
	/**
	 * add a new author to the database
	 * @param a the author to add
	 * @throws SQLException
	 */
	public void addAuthor(Author a) throws SQLException {
		final String q = "INSERT INTO Authors(Name, Address) VALUES(?, ?)";
		List<Author> authors = getAuthors();
		
		//Make sure the author doesn't already exist
		Author currAuth = null;
		for (Author auth : authors) {
			if (auth.getName().equals(a.getName()) && auth.getAddress().equals(a.getAddress()))
				currAuth = auth;
		}
		
		//If he/she doesn't, add him/her
		if (currAuth == null) {
			PreparedStatement pstmt = con.prepareStatement(q);
			pstmt.setString(1, a.getName());
			pstmt.setString(2, a.getAddress());
			
			pstmt.executeUpdate();
		}
	}
	
	/**
	 * Add a cast member to the database
	 * @param cm the cast member to add
	 * @throws SQLException
	 */
	public void addCastMember(CastMember cm) throws SQLException {
		final String q = "INSERT INTO Cast(Name, Address) VALUES(?, ?)";
		List<CastMember> cast = getCast();
		
		//Make sure the cast member doesn't already exist.
		CastMember currCast = null;
		for (CastMember c : cast) {
			if (c.getName().equals(cm.getName()) && c.getAddress().equals(cm.getAddress()))
				currCast = c;
		}
		
		//If he/she doesn't, add him/her.
		if (currCast == null) {
			PreparedStatement pstmt = con.prepareStatement(q);
			pstmt.setString(1, cm.getName());
			pstmt.setString(2, cm.getAddress());
			
			pstmt.executeUpdate();
		}
	}
	
	/**
	 * Add a new book to the database
	 * @param b the book to add
	 * @throws Exception
	 */
	public void addBook(Book b) throws Exception {
		final String q1 = "INSERT INTO Media(Title, Year, Price) VALUES(?, ?, ?)";
		final String q2 = "INSERT INTO Publishers(URL, Name, Address, Phone) VALUES(?, ?, ?, ?)";
		final String q3 = "INSERT INTO Books_PublishedBy(Title, Year, ISBN, Subject, URL) VALUES(?, ?, ?, ?, ?)";
		final String q4 = "INSERT INTO Written_By(Book_Title, Published_Year, Pname, PAddress) VALUES(?, ?, ?, ?)";
		PreparedStatement pstmt;
		List<Media> media = getMedia();
		List<Publisher> pubs = getPublishers();
		
		//Make sure the media doesn't already exist
		Media currMedia = null;
		for (Media m : media) {
			if(m.getTitle().equals(b.getTitle()) && m.getYear() == b.getYear())
				currMedia = m;
		}
		
		//If it does, gtfo
		if(currMedia != null) {
			throw new Exception();
		} 
		
		//Otherwise, add it to the Media table
		pstmt = con.prepareStatement(q1);
		pstmt.setString(1, b.getTitle());
		pstmt.setInt(2, b.getYear());
		pstmt.setFloat(3, b.getPrice());
		pstmt.executeUpdate();
	
		
		//Make sure the publisher doesn't already exist
		Publisher currPub = null;
		for (Publisher p : pubs) {
			if(p.getUrl().equals(b.getPublisher().getUrl()))
				currPub = p;
		}
		
		//If it doesn't, add it to the Publishers table
		if(currPub == null) {
			currPub = b.getPublisher();
			pstmt = con.prepareStatement(q2);
			pstmt.setString(1, currPub.getUrl());
			pstmt.setString(2, currPub.getName());
			pstmt.setString(3, currPub.getAddress());
			pstmt.setString(4, currPub.getPhone());
			pstmt.executeUpdate();
		}
		
		//If we got this far, then we know we are good to go,
		//so add the book to the Books_PublishedBy table.
		pstmt = con.prepareStatement(q3);
		pstmt.setString(1, b.getTitle());
		pstmt.setInt(2, b.getYear());
		pstmt.setString(3, b.getISBN());
		pstmt.setString(4, b.getSubject());
		pstmt.setString(5, currPub.getUrl());
		pstmt.executeUpdate();
		
		//Lastly, add the book's authors to the Authors and WrittenBy tables
		for (Author a : b.getAuthors()) {
			addAuthor(a);
			pstmt = con.prepareStatement(q4);
			pstmt.setString(1, b.getTitle());
			pstmt.setInt(2, b.getYear());
			pstmt.setString(3, a.getName());
			pstmt.setString(4, a.getAddress());
			pstmt.executeUpdate();
		}
	}
	
	/**
	 * Adds a new DVD to the database
	 * @param dvd the dvd to add
	 * @throws Exception
	 */
	public void addDvd(DVD dvd) throws Exception {
		final String q1 = "INSERT INTO Media(Title, Year, Price) VALUES(?, ?, ?)";
		final String q2 = "INSERT INTO DVDs(Title, Year, Director, Genre) VALUES(?, ?, ?, ?)";
		final String q3 = "INSERT INTO Casted_By(Name, Address, Title, Year) VALUES(?, ?, ?, ?)";
		List<Media> media = getMedia();
		
		//Make sure the Media doesn't already exist
		Media currMedia = null;
		for (Media m : media) {
			if(m.getTitle().equals(dvd.getTitle()) && m.getYear() == dvd.getYear())
				currMedia = m;
		}
		
		//If it does, gtfo
		if(currMedia != null) {
			throw new Exception();
		} 
		
		//Otherwise, add it to the Media table
		PreparedStatement pstmt = con.prepareStatement(q1);
		pstmt.setString(1, dvd.getTitle());
		pstmt.setInt(2, dvd.getYear());
		pstmt.setFloat(3, dvd.getPrice());
		pstmt.executeUpdate();
		
		//If we made it this far, we know we are good to go,
		//so add the DVD to the DVDs table
		pstmt = con.prepareStatement(q2);
		pstmt.setString(1, dvd.getTitle());
		pstmt.setInt(2, dvd.getYear());
		pstmt.setString(3, dvd.getDirector());
		pstmt.setString(4, dvd.getGenre());
		pstmt.executeUpdate();
		
		//Lastly, add the DVD's cast members to the Cast and Casted_By tables
		for(CastMember cm : dvd.getCast()) {
			addCastMember(cm);
			pstmt = con.prepareStatement(q3);
			pstmt.setString(1, cm.getName());
			pstmt.setString(2, cm.getAddress());
			pstmt.setString(3, dvd.getTitle());
			pstmt.setInt(4, dvd.getYear());
			pstmt.executeUpdate();		
		}
	}
}

